import logging
import time

from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import os
import sys
import time

from openpyxl.cell import MergedCell
timeStart = time.time()
timeCur = datetime.datetime.now()
# dirPath = 'E:\\日常工作\\11月\\1\\每日必办\\10月考核期'
# dirPath = 'E:\\日常工作\\生成考核情况得分统计表\\2023\\2\\2.10'
dirPath = 'E:\\日常工作\\数据组日常工作-韩冲\\生成每日上大屏文件'
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
logFile = dirPath + 'AutoImportExcel.log'
# 创建logger对象
logger = logging.getLogger('test_logger')
# 设置日志等级
logger.setLevel(logging.DEBUG)
# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('test.log', 'a', encoding='utf-8')
# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)
# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s - %(filename)s - line:%(lineno)d - %(levelname)s - %(message)s -%(process)s')
test_log.setFormatter(formatter)
# 加载文件到logger对象中
logger.addHandler(test_log)
log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)

fileJieDan = dirPath + '\\本考期案件明细表-总量.xlsx'
fileKaoHe = dirPath + '\\本考期案件明细表-考核.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'
fileKaoHePiPei = dirPath + '\\本考期案件明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\本考期案件明细表-总量-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\本考期案件明细表-总量-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = ''
if timeCur.day >= 19:
    fileResultSave = dirPath + '\\{}月考核期裸分情况统计表.xlsx'.format(timeCur.month + 1)
else:
    fileResultSave = dirPath + '\\{}月考核期裸分情况统计表.xlsx'.format(timeCur.month)

bookKaoHe = openpyxl.load_workbook(fileKaoHe)
sheetKaoHe = bookKaoHe.active
maxRowZongLiang = sheetKaoHe.max_row
maxColZongLiang = sheetKaoHe.max_column
logger.debug('本考期案件明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowZongLiang, maxColZongLiang))


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, index_col, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(index_col, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 合并 固定的列
def he_bing_cols(sheet_source, index_title, col_name_1, col_name_2, clo_name_out):
    maxRowSource = sheet_source.max_row
    colIndex1 = get_col_index(sheet_source, index_title, col_name_1)
    colIndex2 = get_col_index(sheet_source, index_title, col_name_2)
    colIndexOut = get_col_index(sheet_source, index_title, clo_name_out)
    for colHeBing in range(index_title+1, maxRowSource+1):
        valueColOne = sheet_source.cell(colHeBing, colIndex1).value
        valueColTwo = sheet_source.cell(colHeBing, colIndex2).value
        if valueColOne is not None:
            valueColOne = valueColOne.strip()
            if valueColTwo is not None:
                valueColTwo = valueColTwo.strip()
                cellValueOut = valueColOne + '\n' + valueColTwo
                sheet_source.cell(colHeBing, colIndexOut).value = cellValueOut


bookQuanBu = openpyxl.load_workbook(fileQuanBu)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))


def pi_pei_cheng_ban_jie_dan(sheet_src, sheet_quan):
    maxRowSource = sheet_src.max_row
    colGongDanSrc = get_col_index(sheet_src, 1, '工单编号')
    colChengBanSrc = get_col_index(sheet_src, 1, '承办部门')
    colJieDanSrc = get_col_index(sheet_src, 1, '接单部门')
    maxRowQuanBuCur = sheet_quan.max_row
    colGongDanQuan = get_col_index(sheet_quan, 1, '工单编号')
    colChengBanQuan = get_col_index(sheet_quan, 1, '承办部门')
    colJieDanQuan = get_col_index(sheet_quan, 1, '接单部门')
    for rowSrc in range(2, maxRowSource+1):
        valueGongDanSrc = sheet_src.cell(rowSrc, colGongDanSrc).value
        if valueGongDanSrc is not None:
            valueGongDanSrc = valueGongDanSrc.strip()
            for rowQuan in range(2, maxRowQuanBuCur+1):
                valueGongDan = sheet_quan.cell(rowQuan, colGongDanQuan).value
                if valueGongDan is not None:
                    if valueGongDan.find(valueGongDanSrc) != -1:
                        strChengBan = sheet_quan.cell(rowQuan, colChengBanQuan).value
                        strJieDan = sheet_quan.cell(rowQuan, colJieDanQuan).value
                        sheet_src.cell(rowSrc, colChengBanSrc).value = strChengBan
                        sheet_src.cell(rowSrc, colJieDanSrc).value = strJieDan
                        logger.debug('{}/{} {}--{}'.format(rowSrc, maxRowSource, valueGongDanSrc, strChengBan))
                        break


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, 1, col_title)
    maxRowSource = sheet_source.max_row
    for rowSource in range(3, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            isFindSource = cellValueSearch.find(col_source)
            if isFindSource != -1:
                sheet_source.cell(rowSource, colTitleIndex).value = col_dest


logger.debug('考核工单-插入承办部门 接单部门 是否处理 用时{}秒'.format(round(time.time() - timeStart, 2)))
colChengBan = get_col_index(sheetKaoHe, 1, '承办单位')
sheetKaoHe.insert_cols(colChengBan, 3)
sheetKaoHe.cell(1, colChengBan).value = '承办部门'
sheetKaoHe.cell(1, colChengBan+1).value = '接单部门'
sheetKaoHe.cell(1, colChengBan+2).value = '是否处理'


logger.debug('考核工单-匹配承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))
pi_pei_cheng_ban_jie_dan(sheetKaoHe, sheetQuanBu)
# 修改特定的承办部门
logger.debug('考核工单-修改特定的承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '综合城管', '综合行政执法队')

# 保存匹配承办部门后的表格
logger.debug('保存匹配承办部门后的表格 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookKaoHe.save(fileKaoHePiPei)
logger.debug('保存  {}'.format(fileKaoHePiPei))

# 打开 本考期案件明细表-总量.xlsx
logger.debug('打开 本考期案件明细表-总量.xlsx 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookJieDan = openpyxl.load_workbook(fileJieDan)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('本考期案件明细表-总量.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

logger.debug('接单总量-插入承办部门 接单部门 是否处理 用时{}秒'.format(round(time.time() - timeStart, 2)))
colChengBanJieDan = get_col_index(sheetJieDan, 1, '承办单位')
sheetJieDan.insert_cols(colChengBanJieDan, 3)
sheetJieDan.cell(1, colChengBanJieDan).value = '承办部门'
sheetJieDan.cell(1, colChengBanJieDan+1).value = '接单部门'
sheetJieDan.cell(1, colChengBanJieDan+2).value = '是否处理'

logger.debug('接单总量-匹配承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))
pi_pei_cheng_ban_jie_dan(sheetJieDan, sheetQuanBu)
# 修改特定的承办部门
logger.debug('接单总量-修改特定的承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '综合城管', '综合行政执法队')

# 保存匹配承办部门后的表格
logger.debug('接单总量-保存匹配承办部门后的表格 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookJieDan.save(fileJieDanPiPei)
logger.debug('保存  {}'.format(fileJieDanPiPei))

colZhuanKaoHe = 7              # 转派-考核
colZhuanLian = 8              # 转派-联系
colZhuanLianNo = 9            # 转派-未联系
colZhuanJie = 10              # 转派-解决
colZhuanJieNo = 11            # 转派-未解决
colZhuanMan = 12              # 转派-满意
colZhuanJiBen = 13            # 转派-基本满意
colZhuanManNo = 14             # 转派-不满意
colZhuanlvLian = 15              # 转派-响应率
colZhuanLvJie = 16             # 转派-解决率
colZhuanLvMan = 17            # 转派-满意率

colZhiKaoHe = 18              # 直派-考核
colZhiLian = 19              # 直派-联系
colZhiLianNo = 20            # 直派-未联系
colZhiJie = 21              # 直派-解决
colZhiJieNo = 22            # 直派-未解决
colZhiMan = 23              # 直派-满意
colZhiJiBen = 24            # 直派-基本满意
colZhiManNo = 25             # 直派-不满意
colZhilvLian = 26              # 直派-响应率
colZhiLvJie = 27             # 直派-解决率
colZhiLvMan = 28            # 直派-满意率

colAvgLian = 29             # 平均响应率
colAvgJie = 30              # 平均解决率
colAvgMan = 31              # 平均满意率


def init_excel_title(sheet_source, str_title, is_she_qu=0):
    if is_she_qu == 1:
        sheet_source.merge_cells(range_string='A1:AJ1')
    else:
        sheet_source.merge_cells(range_string='A1:AI1')

    sheet_source.cell(1, 1).value = str_title
    align = Alignment(horizontal='center', vertical='center', wrapText=True)
    sheet_source.cell(1, 1).alignment = align
    # 设置行高 第一行 40
    sheet_source.row_dimensions[1].height = 60
    sheet_source.row_dimensions[2].height = 35
    sheet_source.row_dimensions[3].height = 35

    # 序号
    sheet_source.merge_cells(range_string='A2:A3')
    sheet_source.cell(2, 1).value = '序号'
    sheet_source.cell(2, 1).alignment = align
    sheet_source.column_dimensions[get_column_letter(1)].width = 6
    # 接单部门
    sheet_source.merge_cells(range_string='B2:B3')
    sheet_source.cell(2, 2).value = '接单部门'
    sheet_source.cell(2, 2).alignment = align
    sheet_source.column_dimensions[get_column_letter(2)].width = 18
    # 接单总量
    sheet_source.merge_cells(range_string='C2:E2')
    sheet_source.cell(2, 3).value = '接单总量'
    sheet_source.cell(2, 3).alignment = align
    # 纳入考核
    sheet_source.merge_cells(range_string='F2:F3')
    sheet_source.cell(2, 6).value = '考核总量'
    sheet_source.cell(2, 6).alignment = align
    # 转派
    sheet_source.merge_cells(range_string='G2:Q2')
    sheet_source.cell(2, 7).value = '转派'
    sheet_source.cell(2, 7).alignment = align
    # 转派-考核(件)
    sheet_source.cell(3, colZhuanKaoHe).value = '考核'
    sheet_source.cell(3, colZhuanKaoHe).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanKaoHe)].width = 5
    # 转派-联系(件)
    sheet_source.cell(3, colZhuanLian).value = '联系'
    sheet_source.cell(3, colZhuanLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanLian)].width = 5
    # 转派-未联系(件)
    sheet_source.cell(3, colZhuanLianNo).value = '未联系'
    sheet_source.cell(3, colZhuanLianNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanLianNo)].width = 5
    # 转派-解决(件)
    sheet_source.cell(3, colZhuanJie).value = '解决'
    sheet_source.cell(3, colZhuanJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanJie)].width = 5
    # 转派-未解决(件)
    sheet_source.cell(3, colZhuanJieNo).value = '未解决'
    sheet_source.cell(3, colZhuanJieNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanJieNo)].width = 5
    # 转派-满意(件)
    sheet_source.cell(3, colZhuanMan).value = '满意'
    sheet_source.cell(3, colZhuanMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanMan)].width = 5
    # 转派-基本满意(件)
    sheet_source.cell(3, colZhuanJiBen).value = '基本满意'
    sheet_source.cell(3, colZhuanJiBen).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanJiBen)].width = 5
    # 转派-不满意(件)
    sheet_source.cell(3, colZhuanManNo).value = '不满意'
    sheet_source.cell(3, colZhuanManNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanManNo)].width = 5
    # 转派-响应率(件)
    sheet_source.cell(3, colZhuanlvLian).value = '响应率'
    sheet_source.cell(3, colZhuanlvLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanlvLian)].width = 5
    # 转派-解决率(件)
    sheet_source.cell(3, colZhuanLvJie).value = '解决率'
    sheet_source.cell(3, colZhuanLvJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanLvJie)].width = 5
    # 转派-满意率(件)
    sheet_source.cell(3, colZhuanLvMan).value = '满意率'
    sheet_source.cell(3, colZhuanLvMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhuanLvMan)].width = 5
    # 直派
    sheet_source.merge_cells(range_string='R2:AB2')
    sheet_source.cell(2, 18).value = '直派'
    sheet_source.cell(2, 18).alignment = align
    # 直派-考核(件)
    sheet_source.cell(3, colZhiKaoHe).value = '考核'
    sheet_source.cell(3, colZhiKaoHe).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiKaoHe)].width = 5
    # 直派-联系(件)
    sheet_source.cell(3, colZhiLian).value = '联系'
    sheet_source.cell(3, colZhiLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiLian)].width = 5
    # 直派-未联系(件)
    sheet_source.cell(3, colZhiLianNo).value = '未联系'
    sheet_source.cell(3, colZhiLianNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiLianNo)].width = 5
    # 直派-解决(件)
    sheet_source.cell(3, colZhiJie).value = '解决'
    sheet_source.cell(3, colZhiJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiJie)].width = 5
    # 直派-未解决(件)
    sheet_source.cell(3, colZhiJieNo).value = '未解决'
    sheet_source.cell(3, colZhiJieNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiJieNo)].width = 5
    # 直派-满意(件)
    sheet_source.cell(3, colZhiMan).value = '满意'
    sheet_source.cell(3, colZhiMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiMan)].width = 5
    # 直派-基本满意(件)
    sheet_source.cell(3, colZhiJiBen).value = '基本满意'
    sheet_source.cell(3, colZhiJiBen).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiJiBen)].width = 5
    # 直派-不满意(件)
    sheet_source.cell(3, colZhiManNo).value = '不满意'
    sheet_source.cell(3, colZhiManNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiManNo)].width = 5
    # 直派-响应率(件)
    sheet_source.cell(3, colZhilvLian).value = '响应率'
    sheet_source.cell(3, colZhilvLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhilvLian)].width = 5
    # 直派-解决率(件)
    sheet_source.cell(3, colZhiLvJie).value = '解决率'
    sheet_source.cell(3, colZhiLvJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiLvJie)].width = 5
    # 直派-满意率(件)
    sheet_source.cell(3, colZhiLvMan).value = '满意率'
    sheet_source.cell(3, colZhiLvMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colZhiLvMan)].width = 5
    # 平均响应率(%)
    sheet_source.merge_cells(range_string='AC2:AC3')
    sheet_source.cell(2, colAvgLian).value = '平均\r\n响应率\r\n(%)'
    sheet_source.cell(2, colAvgLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgLian)].width = 7
    # 平均解决率(%)
    sheet_source.merge_cells(range_string='AD2:AD3')
    sheet_source.cell(2, colAvgJie).value = '平均\r\n解决率\r\n(%)'
    sheet_source.cell(2, colAvgJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgJie)].width = 7
    # 平均满意率(%)
    sheet_source.merge_cells(range_string='AE2:AE3')
    sheet_source.cell(2, colAvgMan).value = '平均\r\n满意率\r\n(%)'
    sheet_source.cell(2, colAvgMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgMan)].width = 7
    # 得分
    sheet_source.merge_cells(range_string='AF2:AF3')
    sheet_source.cell(2, 32).value = '得分'
    sheet_source.cell(2, 32).alignment = align
    sheet_source.column_dimensions[get_column_letter(32)].width = 5
    # 裸分得分
    sheet_source.merge_cells(range_string='AG2:AG3')
    sheet_source.cell(2, 33).value = '排名'
    sheet_source.cell(2, 33).alignment = align
    sheet_source.column_dimensions[get_column_letter(33)].width = 5
    # 裸分失分
    sheet_source.merge_cells(range_string='AH2:AH3')
    sheet_source.cell(2, 34).value = '裸分得分'
    sheet_source.cell(2, 34).alignment = align
    sheet_source.column_dimensions[get_column_letter(34)].width = 5
    # 排名
    sheet_source.merge_cells(range_string='AI2:AI3')
    sheet_source.cell(2, 35).value = '裸分失分'
    sheet_source.cell(2, 35).alignment = align
    sheet_source.column_dimensions[get_column_letter(35)].width = 5

    if is_she_qu == 1:
        # 所属片区
        sheet_source.merge_cells(range_string='AJ2:AJ3')
        sheet_source.cell(2, 36).value = '所属片区'
        sheet_source.cell(2, 36).alignment = align
        sheet_source.column_dimensions[get_column_letter(36)].width = 9

    for rowTitle in range(3, 6):
        sheet_source.column_dimensions[get_column_letter(rowTitle)].width = 5
        if rowTitle % 3 == 0:
            sheet_source.cell(3, rowTitle).value = '总量'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 1:
            sheet_source.cell(3, rowTitle).value = '转派'
            sheet_source.cell(3, rowTitle).alignment = align
        if rowTitle % 3 == 2:
            sheet_source.cell(3, rowTitle).value = '直派'
            sheet_source.cell(3, rowTitle).alignment = align


sheetKeShi = bookKaoHe.create_sheet(title='科室', index=1)
sheetSheQu = bookKaoHe.create_sheet(title='社区、村', index=1)

logger.debug('打开承办部门表 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookChengBabBuMen = openpyxl.load_workbook(fileChengBanBuMen)
sheetChengBabBuMen = bookChengBabBuMen['承办部门']
maxRowChengBabBuMen = sheetChengBabBuMen.max_row
maxColChengBabBuMen = sheetChengBabBuMen.max_column
logger.debug('承办部门.xlsx-------行数：{}行    列数：{}列'.format(maxRowChengBabBuMen, maxColChengBabBuMen))
listKeShi = []
listSheQu = []
listKaoHeZhuan = {}
listKaoHeZhi = {}
listLianXiZhuan = {}
listLianXiZhi = {}
listWeiLianXiZhuan = {}
listWeiLianXiZhi = {}
listJieJueZhuan = {}
listJieJueZhi = {}
listWeiJieJueZhuan = {}
listWeiJieJueZhi = {}
listManYiZhuan = {}
listManYiZhi = {}
listJiBenZhuan = {}
listJiBenZhi = {}
listBuManYiZhuan = {}
listBuManYiZhi = {}

lvLianXiZhi = {}
lvWeiLianXiZhi = {}
lvJieJueZhi = {}
lvWeiJieJueZhi = {}
lvManYiZhi = {}
lvJiBenZhi = {}
lvBuManYiZhi = {}
lvManYiFinalZhi = {}
lvLianXiZhuan = {}
lvWeiLianXiZhuan = {}
lvJieJueZhuan = {}
lvWeiJieJueZhuan = {}
lvManYiZhuan = {}
lvJiBenZhuan = {}
lvBuManYiZhuan = {}
lvManYiFinalZhuan = {}

lvLianXiAverage = {}
lvWeiLianXiAverage = {}
lvJieJueAverage = {}
lvWeiJieJueAverage = {}
lvManYiAverage = {}
lvJiBenAverage = {}
lvBuManYiAverage = {}
lvManYiFinalAverage = {}

listJieDanZhi = {}
listJieDanZhuan = {}

deFenLuoFen = {}
shiFenLuoFen = {}

# 社区村的所属片区
suoShuPianQu = {}

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))

for rowKeshiList in range(3, maxRowChengBabBuMen+1):
    valueCellKeShi = sheetChengBabBuMen.cell(rowKeshiList, 1).value
    if valueCellKeShi is not None:
        listKeShi.append(valueCellKeShi)
        listKaoHeZhuan[valueCellKeShi] = 0
        listKaoHeZhi[valueCellKeShi] = 0
        listLianXiZhuan[valueCellKeShi] = 0
        listLianXiZhi[valueCellKeShi] = 0
        listJieJueZhuan[valueCellKeShi] = 0
        listJieJueZhi[valueCellKeShi] = 0
        listManYiZhuan[valueCellKeShi] = 0
        listManYiZhi[valueCellKeShi] = 0
        listJiBenZhuan[valueCellKeShi] = 0
        listJiBenZhi[valueCellKeShi] = 0
        listWeiLianXiZhuan[valueCellKeShi] = 0
        listWeiLianXiZhi[valueCellKeShi] = 0
        listWeiJieJueZhuan[valueCellKeShi] = 0
        listWeiJieJueZhi[valueCellKeShi] = 0
        listBuManYiZhuan[valueCellKeShi] = 0
        listBuManYiZhi[valueCellKeShi] = 0
        listJieDanZhi[valueCellKeShi] = 0
        listJieDanZhuan[valueCellKeShi] = 0
        lvLianXiZhi[valueCellKeShi] = None
        lvWeiLianXiZhi[valueCellKeShi] = None
        lvJieJueZhi[valueCellKeShi] = None
        lvWeiJieJueZhi[valueCellKeShi] = None
        lvManYiZhi[valueCellKeShi] = None
        lvJiBenZhi[valueCellKeShi] = None
        lvBuManYiZhi[valueCellKeShi] = None
        lvManYiFinalZhi[valueCellKeShi] = None
        lvManYiFinalZhi[valueCellKeShi] = None
        lvLianXiZhuan[valueCellKeShi] = None
        lvWeiLianXiZhuan[valueCellKeShi] = None
        lvJieJueZhuan[valueCellKeShi] = None
        lvWeiJieJueZhuan[valueCellKeShi] = None
        lvManYiZhuan[valueCellKeShi] = None
        lvJiBenZhuan[valueCellKeShi] = None
        lvBuManYiZhuan[valueCellKeShi] = None
        lvManYiFinalZhuan[valueCellKeShi] = None
        lvLianXiAverage[valueCellKeShi] = None
        lvWeiLianXiAverage[valueCellKeShi] = None
        lvJieJueAverage[valueCellKeShi] = None
        lvWeiJieJueAverage[valueCellKeShi] = None
        lvManYiAverage[valueCellKeShi] = None
        lvJiBenAverage[valueCellKeShi] = None
        lvBuManYiAverage[valueCellKeShi] = None
        lvManYiFinalAverage[valueCellKeShi] = None
        deFenLuoFen[valueCellKeShi] = None
        shiFenLuoFen[valueCellKeShi] = None

for rowSheQuList in range(3, maxRowChengBabBuMen+1):
    valueCellSheQu = sheetChengBabBuMen.cell(rowSheQuList, 2).value
    if valueCellSheQu is not None:
        listSheQu.append(valueCellSheQu)
        listKaoHeZhuan[valueCellSheQu] = 0
        listKaoHeZhi[valueCellSheQu] = 0
        listLianXiZhuan[valueCellSheQu] = 0
        listLianXiZhi[valueCellSheQu] = 0
        listJieJueZhuan[valueCellSheQu] = 0
        listJieJueZhi[valueCellSheQu] = 0
        listManYiZhuan[valueCellSheQu] = 0
        listManYiZhi[valueCellSheQu] = 0
        listJiBenZhuan[valueCellSheQu] = 0
        listJiBenZhi[valueCellSheQu] = 0
        listWeiLianXiZhuan[valueCellSheQu] = 0
        listWeiLianXiZhi[valueCellSheQu] = 0
        listWeiJieJueZhuan[valueCellSheQu] = 0
        listWeiJieJueZhi[valueCellSheQu] = 0
        listBuManYiZhuan[valueCellSheQu] = 0
        listBuManYiZhi[valueCellSheQu] = 0
        listJieDanZhi[valueCellSheQu] = 0
        listJieDanZhuan[valueCellSheQu] = 0
        lvLianXiZhi[valueCellSheQu] = None
        lvWeiLianXiZhi[valueCellSheQu] = None
        lvJieJueZhi[valueCellSheQu] = None
        lvWeiJieJueZhi[valueCellSheQu] = None
        lvManYiZhi[valueCellSheQu] = None
        lvJiBenZhi[valueCellSheQu] = None
        lvBuManYiZhi[valueCellSheQu] = None
        lvManYiFinalZhi[valueCellSheQu] = None
        lvLianXiZhuan[valueCellSheQu] = None
        lvWeiLianXiZhuan[valueCellSheQu] = None
        lvJieJueZhuan[valueCellSheQu] = None
        lvWeiJieJueZhuan[valueCellSheQu] = None
        lvManYiZhuan[valueCellSheQu] = None
        lvJiBenZhuan[valueCellSheQu] = None
        lvBuManYiZhuan[valueCellSheQu] = None
        lvManYiFinalZhuan[valueCellSheQu] = None
        lvLianXiAverage[valueCellSheQu] = None
        lvWeiLianXiAverage[valueCellSheQu] = None
        lvJieJueAverage[valueCellSheQu] = None
        lvWeiJieJueAverage[valueCellSheQu] = None
        lvManYiAverage[valueCellSheQu] = None
        lvJiBenAverage[valueCellSheQu] = None
        lvBuManYiAverage[valueCellSheQu] = None
        lvManYiFinalAverage[valueCellSheQu] = None
        deFenLuoFen[valueCellSheQu] = None
        shiFenLuoFen[valueCellSheQu] = None
        suoShuPianQu[valueCellSheQu] = sheetChengBabBuMen.cell(rowSheQuList, 3).value

logger.debug('承办部门-科室：{}'.format(listKeShi))
logger.debug('承办部门-社区/村：{}'.format(listSheQu))

colChengBanJieDan = get_col_index(sheetJieDan, 1, '承办部门')
colLeiXingJieDan = get_col_index(sheetJieDan, 1, '派单类型')
colJieDanChuLi = get_col_index(sheetJieDan, 1, '是否处理')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
logger.debug('开始统计接单数据 用时{}秒'.format(round(time.time() - timeStart, 2)))
# lineIndexJieDan = 2
# while lineIndexJieDan <= maxRowJieDan:
numWeiChuLi = 0
# while lineIndexJieDan <= maxRowJieDan:
for lineIndexJieDan in range(2, maxRowJieDan+1):
    valueChengBanJieDan = sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value
    valueLeiXingJieDan = sheetJieDan.cell(lineIndexJieDan, colLeiXingJieDan).value
    if valueChengBanJieDan is not None and valueLeiXingJieDan is not None:
        valueChengBanJieDan = valueChengBanJieDan.strip()
        valueLeiXingJieDan = valueLeiXingJieDan.strip()
        logger.debug('{} - {}'.format(valueChengBanJieDan, valueLeiXingJieDan))
        if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
            if valueLeiXingJieDan == '直派' or valueLeiXingJieDan == '市派':
                listJieDanZhi[valueChengBanJieDan] = listJieDanZhi[valueChengBanJieDan] + 1
                # sheetJieDan.delete_rows(lineIndexJieDan)
                # maxRowJieDan = maxRowJieDan - 1
                sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '已处理-直派'
            else:
                if valueLeiXingJieDan == '转派' or valueLeiXingJieDan == '区派':
                    listJieDanZhuan[valueChengBanJieDan] = listJieDanZhuan[valueChengBanJieDan] + 1
                    # sheetJieDan.delete_rows(lineIndexJieDan)
                    # maxRowJieDan = maxRowJieDan - 1
                    sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '已处理-转派'
                else:
                    # lineIndexJieDan = lineIndexJieDan + 1
                    numWeiChuLi = numWeiChuLi + 1
                    sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理'
        else:
            # lineIndexJieDan = lineIndexJieDan + 1
            numWeiChuLi = numWeiChuLi + 1
            sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理-无承办部门'
    else:
        # lineIndexJieDan = lineIndexJieDan + 1
        numWeiChuLi = numWeiChuLi + 1
        sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理-数据错误'

logger.debug('接单量-直派：{}'.format(listJieDanZhi))
logger.debug('接单量-转派：{}'.format(listJieDanZhuan))
logger.debug('统计接单数据完成 用时{}秒'.format(round(time.time() - timeStart, 2)))
# maxRowJieDan2 = sheetJieDan.max_row
if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))

colChengBanIndex = get_col_index(sheetKaoHe, 1, '承办部门')
colJieDanIndex = get_col_index(sheetKaoHe, 1, '接单部门')
colLeiXing = get_col_index(sheetKaoHe, 1, '派单类型')
colLianXi = get_col_index(sheetKaoHe, 1, '是否联系')
colJieJue = get_col_index(sheetKaoHe, 1, '是否解决')
colManYi = get_col_index(sheetKaoHe, 1, '是否满意')
colChuLi = get_col_index(sheetKaoHe, 1, '是否处理')
logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('接单部门：{}列'.format(colJieDanIndex))
logger.debug('派单类型：{}列'.format(colLeiXing))
logger.debug('是否联系：{}列'.format(colLianXi))
logger.debug('是否解决：{}列'.format(colJieJue))
logger.debug('是否满意：{}列'.format(colManYi))
logger.debug('是否处理：{}列'.format(colChuLi))
logger.debug('开始统计考核数据 用时{}秒'.format(round(time.time() - timeStart, 2)))
lineIndex = 2
numWeiChuLiKaoHe = 0
# while lineIndex <= maxRowQuanBu:
for lineIndex in range(2, maxRowZongLiang+1):
    valueChengBanCur = sheetKaoHe.cell(lineIndex, colChengBanIndex).value
    valueJieDanCur = sheetKaoHe.cell(lineIndex, colJieDanIndex).value
    valueLeiXing = sheetKaoHe.cell(lineIndex, colLeiXing).value
    valueLianxi = sheetKaoHe.cell(lineIndex, colLianXi).value
    valueJieJue = sheetKaoHe.cell(lineIndex, colJieJue).value
    valueManYi = sheetKaoHe.cell(lineIndex, colManYi).value
    if valueChengBanCur is not None and valueLeiXing is not None \
        and valueLianxi is not None and valueJieJue is not None and valueManYi is not None:
        valueChengBanCur = valueChengBanCur.strip()
        valueLeiXing = valueLeiXing.strip()
        valueLianxi = valueLianxi.strip()
        valueJieJue = valueJieJue.strip()
        valueManYi = valueManYi.strip()
        logger.debug('{} - {} - {}'.format(valueChengBanCur, valueLeiXing, valueJieDanCur))
        if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
            if valueLeiXing == '市派' or valueLeiXing == '直派':
                listKaoHeZhi[valueChengBanCur] = listKaoHeZhi[valueChengBanCur] + 1
                if valueLianxi == '已联系' or valueLianxi == '联系':
                    listLianXiZhi[valueChengBanCur] = listLianXiZhi[valueChengBanCur] + 1
                if valueLianxi == '未联系':
                    listWeiLianXiZhi[valueChengBanCur] = listWeiLianXiZhi[valueChengBanCur] + 1
                if valueJieJue == '已解决' or valueJieJue == '解决':
                    listJieJueZhi[valueChengBanCur] = listJieJueZhi[valueChengBanCur] + 1
                if valueJieJue == '未解决':
                    listWeiJieJueZhi[valueChengBanCur] = listWeiJieJueZhi[valueChengBanCur] + 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listManYiZhi[valueChengBanCur] = listManYiZhi[valueChengBanCur] + 1
                if valueManYi == '基本满意':
                    listJiBenZhi[valueChengBanCur] = listJiBenZhi[valueChengBanCur] + 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listBuManYiZhi[valueChengBanCur] = listBuManYiZhi[valueChengBanCur] + 1
                # sheetKaoHe.delete_rows(lineIndex)
                # maxRowQuanBu = maxRowQuanBu - 1
                sheetKaoHe.cell(lineIndex, colChuLi).value = '已处理-直派'
            else:
                if valueLeiXing == '区派' or valueLeiXing == '转派':
                    listKaoHeZhuan[valueChengBanCur] = listKaoHeZhuan[valueChengBanCur] + 1
                    if valueLianxi == '已联系' or valueLianxi == '联系':
                        listLianXiZhuan[valueChengBanCur] = listLianXiZhuan[valueChengBanCur] + 1
                    if valueLianxi == '未联系':
                        listWeiLianXiZhuan[valueChengBanCur] = listWeiLianXiZhuan[valueChengBanCur] + 1
                    if valueJieJue == '已解决'  or valueJieJue == '解决':
                        listJieJueZhuan[valueChengBanCur] = listJieJueZhuan[valueChengBanCur] + 1
                    if valueJieJue == '未解决':
                        listWeiJieJueZhuan[valueChengBanCur] = listWeiJieJueZhuan[valueChengBanCur] + 1
                    if valueManYi == '满意' or valueManYi == '非常满意':
                        listManYiZhuan[valueChengBanCur] = listManYiZhuan[valueChengBanCur] + 1
                    if valueManYi == '基本满意':
                        listJiBenZhuan[valueChengBanCur] = listJiBenZhuan[valueChengBanCur] + 1
                    if valueManYi == '不满意' or valueManYi == '非常不满意':
                        listBuManYiZhuan[valueChengBanCur] = listBuManYiZhuan[valueChengBanCur] + 1
                    # sheetKaoHe.delete_rows(lineIndex)
                    # maxRowQuanBu = maxRowQuanBu - 1
                    sheetKaoHe.cell(lineIndex, colChuLi).value = '已处理-转派'
                else:
                    # lineIndex = lineIndex + 1
                    sheetKaoHe.cell(lineIndex, colChuLi).value = '未处理'
        else:
            # lineIndex = lineIndex + 1
            sheetKaoHe.cell(lineIndex, colChuLi).value = ' 未处理 - 无承办部门'
            numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1
    else:
        # lineIndex = lineIndex + 1
        sheetKaoHe.cell(lineIndex, colChuLi).value = ' 未处理 - 数据错误'
        numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))
logger.debug('统计考核数据完成 用时{}秒'.format(round(time.time() - timeStart, 2)))

countJieDanZhi = 0
countJieDanZhuan = 0
countKaoHeZhi = 0
countKaoHeZhuan = 0
countLianXiZhi = 0
countLianXiZhuan = 0
countWeiLianXiZhi = 0
countWeiLianXiZhuan = 0
countJieJueZhi = 0
countJieJueZhuan = 0
countWeiJieJueZhi = 0
countWeiJieJueZhuan = 0
countManYiZhi = 0
countManYiZhuan = 0
countJiBenZhi = 0
countJIBenZhuan = 0
countBuManYiZhi = 0
countBuManYiZhuan = 0
countKaoHeZhiKeShi = 0
countKaoHeZhuanKeShi = 0
countKaoHeZhiSheQu = 0
countKaoHeZhuanSheQu = 0

for indexTongJi in range(0, len(listKeShi)):
    nameTongJi = listKeShi[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiKeShi = countKaoHeZhiKeShi + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanKeShi = countKaoHeZhuanKeShi + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]


for indexTongJi in range(0, len(listSheQu)):
    nameTongJi = listSheQu[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiSheQu = countKaoHeZhiSheQu + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanSheQu = countKaoHeZhuanSheQu + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]

logger.debug('考核-转派：{}'.format(countKaoHeZhuan))
logger.debug('联系-转派：{}'.format(countLianXiZhuan))
logger.debug('未联系-转派：{}'.format(countWeiLianXiZhuan))
logger.debug('解决-转派：{}'.format(countJieJueZhuan))
logger.debug('未解决-转派：{}'.format(countWeiJieJueZhuan))
logger.debug('满意-转派：{}'.format(countManYiZhuan))
logger.debug('基本满意-转派：{}'.format(countJIBenZhuan))
logger.debug('不满意-转派：{}'.format(countBuManYiZhuan))

logger.debug('考核-直派：{}'.format(countKaoHeZhi))
logger.debug('联系-直派：{}'.format(countLianXiZhi))
logger.debug('未联系-直派：{}'.format(countWeiLianXiZhi))
logger.debug('解决-直派：{}'.format(countJieJueZhi))
logger.debug('未解决-直派：{}'.format(countWeiJieJueZhi))
logger.debug('满意-直派：{}'.format(countManYiZhi))
logger.debug('基本满意-直派：{}'.format(countJiBenZhi))
logger.debug('不满意-直派：{}'.format(countBuManYiZhi))

lvLianxiCount = round((round(countLianXiZhuan/countKaoHeZhuan*100, 2) + round(countLianXiZhi/countKaoHeZhi*100, 2))/2, 2)
lvJieJueCount = round((round(countJieJueZhuan/countKaoHeZhuan*100, 2) + round(countJieJueZhi/countKaoHeZhi*100, 2))/2, 2)
lvManYiCount = round((round(countManYiZhuan/countKaoHeZhuan*100, 2) + round(countManYiZhi/countKaoHeZhi*100, 2))/2, 2)
lvJiBenCount = round((round(countJIBenZhuan/countKaoHeZhuan*100, 2) + round(countJiBenZhi/countKaoHeZhi*100, 2))/2, 2)
lvManYiFinalCount = round(lvManYiCount + lvJiBenCount*0.8, 2)
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))

lvLianxiCount = round((round(countLianXiZhuan/countKaoHeZhuan*100) + round(countLianXiZhi/countKaoHeZhi*100))/2)
lvJieJueCount = round((round(countJieJueZhuan/countKaoHeZhuan*100) + round(countJieJueZhi/countKaoHeZhi*100))/2)
lvManYiCount = round((round(countManYiZhuan/countKaoHeZhuan*100) + round(countManYiZhi/countKaoHeZhi*100))/2)
lvJiBenCount = round((round(countJIBenZhuan/countKaoHeZhuan*100) + round(countJiBenZhi/countKaoHeZhi*100))/2)
lvManYiFinalCount = round((lvManYiCount + lvJiBenCount*0.8))
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))

lvLianxiCount = round((round(countLianXiZhuan/countKaoHeZhuan*100) + round(countLianXiZhi/countKaoHeZhi*100))/2, 2)
lvJieJueCount = round((round(countJieJueZhuan/countKaoHeZhuan*100) + round(countJieJueZhi/countKaoHeZhi*100))/2, 2)
lvManYiCount = round((round(countManYiZhuan/countKaoHeZhuan*100) + round(countManYiZhi/countKaoHeZhi*100))/2, 2)
lvJiBenCount = round((round(countJIBenZhuan/countKaoHeZhuan*100) + round(countJiBenZhi/countKaoHeZhi*100))/2, 2)
lvManYiFinalCount = round(lvManYiCount + lvJiBenCount*0.8, 2)
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))

lvLianxiCount = round((countLianXiZhuan/countKaoHeZhuan*100 + countLianXiZhi/countKaoHeZhi*100)/2)
lvJieJueCount = round((countJieJueZhuan/countKaoHeZhuan*100 + countJieJueZhi/countKaoHeZhi*100)/2)
lvManYiCount = round((countManYiZhuan/countKaoHeZhuan*100 + countManYiZhi/countKaoHeZhi*100)/2)
lvJiBenCount = round((countJIBenZhuan/countKaoHeZhuan*100 + countJiBenZhi/countKaoHeZhi*100)/2)
lvManYiFinalCount = round((lvManYiCount + lvJiBenCount*0.8))
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))

luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))

lvLianxiCount = round((countLianXiZhuan/countKaoHeZhuan*100 + countLianXiZhi/countKaoHeZhi*100)/2, 2)
lvJieJueCount = round((countJieJueZhuan/countKaoHeZhuan*100 + countJieJueZhi/countKaoHeZhi*100)/2, 2)
lvManYiCount = round((countManYiZhuan/countKaoHeZhuan*100 + countManYiZhi/countKaoHeZhi*100)/2, 2)
lvJiBenCount = round((countJIBenZhuan/countKaoHeZhuan*100 + countJiBenZhi/countKaoHeZhi*100)/2, 2)
lvManYiFinalCount = round(lvManYiCount + lvJiBenCount*0.8, 2)
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} '
             '基本满意率：{}'.format(lvLianxiCount, lvJieJueCount, lvManYiFinalCount, lvManYiCount, lvJiBenCount))
logger.debug('响应率：{}  解决率：{}  满意率：{} 双满率：{} 基本满意率：'
             '{}'.format(round(lvLianxiCount), round(lvJieJueCount)
                         , round(lvManYiFinalCount), round(lvManYiCount), round(lvJiBenCount)))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))


def make_pai_ming_shu_ju(list_src):
    for rowIndex in range(0, len(list_src)):
        buMenName = list_src[rowIndex]
        if listKaoHeZhi[buMenName] != 0:
            lvLianXiZhi[buMenName] = round(listLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvWeiLianXiZhi[buMenName] = round(listWeiLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvJieJueZhi[buMenName] = round(listJieJueZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvWeiJieJueZhi[buMenName] = round(listWeiJieJueZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiZhi[buMenName] = round(listManYiZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvJiBenZhi[buMenName] = round(listJiBenZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvBuManYiZhi[buMenName] = round(listBuManYiZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiFinalZhi[buMenName] = round(lvManYiZhi[buMenName] + lvJiBenZhi[buMenName]*0.8)
        if listKaoHeZhuan[buMenName] != 0:
            lvLianXiZhuan[buMenName] = round(listLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiLianXiZhuan[buMenName] = round(listWeiLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJieJueZhuan[buMenName] = round(listJieJueZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiJieJueZhuan[buMenName] = round(listWeiJieJueZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiZhuan[buMenName] = round(listManYiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJiBenZhuan[buMenName] = round(listJiBenZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvBuManYiZhuan[buMenName] = round(listBuManYiZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiFinalZhuan[buMenName] = round(lvManYiZhuan[buMenName] + lvJiBenZhuan[buMenName] * 0.8)
        # 联系率计算平均值
        deFenLianXi = round((listLianXiZhi[buMenName]/countKaoHeZhi*100
                             + listLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        shiFenLianXi = round((listWeiLianXiZhi[buMenName]/countKaoHeZhi*100
                             + listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        deFenJieJue = round((listJieJueZhi[buMenName]/countKaoHeZhi*100
                             + listJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        shiFenJieJue = round((listWeiJieJueZhi[buMenName]/countKaoHeZhi*100
                             + listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        deFenShangMan = round((listManYiZhi[buMenName]/countKaoHeZhi*100
                               + listManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        deFenJiBen = round((listJiBenZhi[buMenName]/countKaoHeZhi*100
                            + listJiBenZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        ShiFenBuManYi = round((listBuManYiZhi[buMenName]/countKaoHeZhi*100
                               + listBuManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        deFenManYi = round(deFenShangMan + deFenJiBen*0.8, 2)
        shiFenManYi = round(ShiFenBuManYi + deFenJiBen*0.2, 2)

        deFenLuoFen[buMenName] = round(deFenLianXi*0.1+deFenJieJue*0.5+deFenManYi*0.4, 2)
        shiFenLuoFen[buMenName] = round(shiFenLianXi*0.1+shiFenJieJue*0.5+shiFenManYi*0.4, 2)

        if lvLianXiZhi[buMenName] is not None and lvLianXiZhuan[buMenName] is not None:
            lvLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvLianXiZhuan[buMenName]) / 2)
        else:
            if lvLianXiZhi[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhi[buMenName]
            if lvLianXiZhuan[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhuan[buMenName]

        # 未联系率计算平均值
        if lvWeiLianXiZhi[buMenName] is not None and lvWeiLianXiZhuan[buMenName] is not None:
            lvWeiLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvWeiLianXiZhuan[buMenName]) / 2)
        else:
            if lvWeiLianXiZhi[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhi[buMenName]
            if lvWeiLianXiZhuan[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhuan[buMenName]

        # 解决率计算平均值
        if lvJieJueZhi[buMenName] is not None and lvJieJueZhuan[buMenName] is not None:
            lvJieJueAverage[buMenName] = round((lvJieJueZhi[buMenName] + lvJieJueZhuan[buMenName]) / 2)
        else:
            if lvJieJueZhi[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhi[buMenName]
            if lvJieJueZhuan[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhuan[buMenName]

        # 未解决率计算平均值
        if lvWeiJieJueZhi[buMenName] is not None and lvWeiJieJueZhuan[buMenName] is not None:
            lvWeiJieJueAverage[buMenName] = round((lvWeiJieJueZhi[buMenName] + lvWeiJieJueZhuan[buMenName]) / 2)
        else:
            if lvWeiJieJueZhi[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhi[buMenName]
            if lvWeiJieJueZhuan[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhuan[buMenName]

        # 满意率计算平均值
        if lvManYiZhi[buMenName] is not None and lvManYiZhuan[buMenName] is not None:
            lvManYiAverage[buMenName] = round((lvManYiZhi[buMenName] + lvManYiZhuan[buMenName]) / 2)
        else:
            if lvManYiZhi[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhi[buMenName]
            if lvManYiZhuan[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhuan[buMenName]

        # 基本满意率计算平均值
        if lvJiBenZhi[buMenName] is not None and lvJiBenZhuan[buMenName] is not None:
            lvJiBenAverage[buMenName] = round((lvJiBenZhi[buMenName] + lvJiBenZhuan[buMenName]) / 2)
        else:
            if lvJiBenZhi[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhi[buMenName]
            if lvJiBenZhuan[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhuan[buMenName]

        # 不满意率计算平均值
        if lvBuManYiZhi[buMenName] is not None and lvBuManYiZhuan[buMenName] is not None:
            lvBuManYiAverage[buMenName] = round((lvBuManYiZhi[buMenName] + lvBuManYiZhuan[buMenName]) / 2)
        else:
            if lvBuManYiZhi[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhi[buMenName]
            if lvBuManYiZhuan[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhuan[buMenName]

        # 最终满意率计算平均值
        if lvManYiFinalZhi[buMenName] is not None and lvManYiFinalZhuan[buMenName] is not None:
            lvManYiFinalAverage[buMenName] = round((lvManYiFinalZhi[buMenName] + lvManYiFinalZhuan[buMenName]) / 2)
        else:
            if lvManYiFinalZhi[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhi[buMenName]
            if lvManYiFinalZhuan[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhuan[buMenName]

        logger.debug('{} 直派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{}'
                     .format(buMenName, listKaoHeZhi[buMenName], listLianXiZhi[buMenName], lvLianXiZhi[buMenName]
                             , listJieJueZhi[buMenName], lvJieJueZhi[buMenName]
                             , listManYiZhi[buMenName], listJiBenZhi[buMenName], lvManYiFinalZhi[buMenName]))
        logger.debug('{} 转派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{}'
                     .format(buMenName, listKaoHeZhuan[buMenName], listLianXiZhuan[buMenName], lvLianXiZhuan[buMenName]
                             , listJieJueZhuan[buMenName], lvJieJueZhuan[buMenName]
                             , listManYiZhuan[buMenName], listJiBenZhuan[buMenName], lvManYiFinalZhuan[buMenName]))
        logger.debug('{} 平均  联系率：{}  解决率：{} 满意率：{}'
                     .format(buMenName, lvLianXiAverage[buMenName], lvJieJueAverage[buMenName],
                             lvManYiFinalAverage[buMenName]))


make_pai_ming_shu_ju(listKeShi)
make_pai_ming_shu_ju(listSheQu)

# '拱辰街道科室诉求总量反映情况统计（2022年10月19日0时至10月31日9时）'
strTitleKeShi = '拱辰街道科室裸分情况统计表\r\n（'
if timeCur.day <= 19:
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month-1)
    strMain = '{}年{}月19日0时至'.format(timeCur.year, timeCur.month-1)
else:
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
    strMain = '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
strTitleKeShi = strTitleKeShi + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
strMain = strMain + '{}月{}日9时，'.format(timeCur.month, timeCur.day)

# 拱辰街道社区、村诉求考核反映情况统计（2022年9月19日0时至10月8日12时）
strTitleSheQu = '拱辰街道社区、村裸分情况统计表\r\n（'
if timeCur.day <= 19:
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month-1)
else:
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
strTitleSheQu = strTitleSheQu + '{}月{}日9时)'.format(timeCur.month, timeCur.day)

init_excel_title(sheetKeShi, strTitleKeShi)
init_excel_title(sheetSheQu, strTitleSheQu, 1)

strMain = strMain + '我街道市民诉求处置中心接收诉求{}件，'.format(countJieDanZhi+countJieDanZhuan)
strMain = strMain + '纳入考核{}件，'.format(countKaoHeZhi+countKaoHeZhuan)
strMain = strMain + '联系{}件，'.format(countLianXiZhi+countLianXiZhuan)
strMain = strMain + '未联系{}件，'.format(countWeiLianXiZhi+countWeiLianXiZhuan)
strMain = strMain + '联系率{}%；'.format(round(lvLianxiCount))
strMain = strMain + '解决{}件，'.format(countJieJueZhi+countJieJueZhuan)
strMain = strMain + '未解决{}件，'.format(countWeiJieJueZhi+countWeiJieJueZhuan)
strMain = strMain + '解决率{}%；'.format(round(lvJieJueCount))
strMain = strMain + '满意{}件，'.format(countManYiZhi+countManYiZhuan)
strMain = strMain + '基本满意{}件，'.format(countJiBenZhi+countJIBenZhuan)
strMain = strMain + '不满意{}件，'.format(countBuManYiZhi+countBuManYiZhuan)
strMain = strMain + '满意率{}%；综合得分{}。'.format(round(lvManYiFinalCount), round(luoFen))


def insert_into_sheet(sheet_insert, inset_bu_men, de_fen, is_she_qu=0):
    rowCount = sheet_insert.max_row
    rowInsert = rowCount + 1
    colDeFen = get_col_index(sheet_insert, 2, '得分')
    if de_fen is not None:
        for rowPai in range(4, rowCount+1):
            deFenRow = sheet_insert.cell(rowPai, colDeFen).value
            if deFenRow is not None:
                if de_fen >= deFenRow:
                    sheet_insert.insert_rows(rowPai)
                    rowInsert = rowPai
                    break
            else:
                sheet_insert.insert_rows(rowPai)
                rowInsert = rowPai
                break
    sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 接单部门
    logger.debug('{} 接单量：{}  转派：{}   '
                 '直派：{} '.format(inset_bu_men, listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
                                   , listJieDanZhuan[inset_bu_men], listJieJueZhi[inset_bu_men]))
    if listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 3).value = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    if listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 4).value = listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    if listJieDanZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 5).value = listJieDanZhi[inset_bu_men]  # 接单总量(件)-直派
    if listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 6).value = listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men]  # 纳入考核(件)-总量
    if listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanKaoHe).value = listKaoHeZhuan[inset_bu_men]  # 转派-考核
    if listLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLian).value = listLianXiZhuan[inset_bu_men]  # 转派-联系
    if listWeiLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLianNo).value = listWeiLianXiZhuan[inset_bu_men]  # 转派-未联系
    if listJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJie).value = listJieJueZhuan[inset_bu_men]  # 转派-解决
    if listWeiJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJieNo).value = listWeiJieJueZhuan[inset_bu_men]  # 转派-未解决
    if listManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanMan).value = listManYiZhuan[inset_bu_men]  # 转派-满意
    if listJiBenZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJiBen).value = listJiBenZhuan[inset_bu_men]  # 转派-基本满意
    if listBuManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanManNo).value = listBuManYiZhuan[inset_bu_men]  # 转派-不满意
    if lvLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanlvLian).value = lvLianXiZhuan[inset_bu_men]  # 转派-响应率
    if lvJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLvJie).value = lvJieJueZhuan[inset_bu_men]  # 转派-解决率
    if lvManYiFinalZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLvMan).value = lvManYiFinalZhuan[inset_bu_men]  # 转派-满意率
    if listKaoHeZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiKaoHe).value = listKaoHeZhi[inset_bu_men]  # 直派-考核
    if listLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLian).value = listLianXiZhi[inset_bu_men]  # 直派-联系
    if listWeiLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLianNo).value = listWeiLianXiZhi[inset_bu_men]  # 直派-未联系
    if listJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJie).value = listJieJueZhi[inset_bu_men]  # 直派-解决
    if listWeiJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJieNo).value = listWeiJieJueZhi[inset_bu_men]  # 直派-未解决
    if listManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiMan).value = listManYiZhi[inset_bu_men]  # 直派-满意
    if listJiBenZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJiBen).value = listJiBenZhi[inset_bu_men]  # 直派-基本满意
    if listBuManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiManNo).value = listBuManYiZhi[inset_bu_men]  # 直派-不满意
    if lvLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhilvLian).value = lvLianXiZhi[inset_bu_men]  # 直派-响应率
    if lvJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLvJie).value = lvJieJueZhi[inset_bu_men]  # 直派-解决率
    if lvManYiFinalZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLvMan).value = lvManYiFinalZhi[inset_bu_men]  # 直派-满意率
    if lvLianXiAverage[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colAvgLian).value = lvLianXiAverage[inset_bu_men]  # 平均响应率
    if lvJieJueAverage[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colAvgJie).value = lvJieJueAverage[inset_bu_men]  # 平均解决率
    if lvManYiFinalAverage[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colAvgMan).value = lvManYiFinalAverage[inset_bu_men]  # 平均满意率
    sheet_insert.cell(rowInsert, 32).value = de_fen  # 得分
    if deFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 34).value = deFenLuoFen[inset_bu_men]  # 裸分得分
    if shiFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 35).value = shiFenLuoFen[inset_bu_men]  # 裸分失分
    if is_she_qu == 1:
        sheet_insert.cell(rowInsert, 36).value = suoShuPianQu[inset_bu_men]  # 裸分失分


def make_pai_ming(sheet_make, list_make, is_she_qu=0):
    for indexName in range(0, len(list_make)):
        nameIndex = list_make[indexName]
        numDeFen = None
        lvManYiAll = None
        if listKaoHeZhi[nameIndex] + listKaoHeZhuan[nameIndex] > 0:
            if lvManYiAverage[nameIndex] is not None and lvJiBenAverage[nameIndex] is not None:
                lvManYiAll = lvManYiAverage[nameIndex]+lvJiBenAverage[nameIndex]*0.8
            else:
                if lvManYiAverage[nameIndex] is not None:
                    lvManYiAll = lvManYiAverage[nameIndex]
                if lvJiBenAverage[nameIndex] is not None:
                    lvManYiAll = lvJiBenAverage[nameIndex]*0.8
            numDeFen = round(lvLianXiAverage[nameIndex] * 0.1 + lvJieJueAverage[nameIndex] * 0.5 + lvManYiAll * 0.4)
        # logger.debug('{} 得分：{} '.format(nameIndex, numDeFen))
        insert_into_sheet(sheet_make, nameIndex, numDeFen, is_she_qu)


make_pai_ming(sheetKeShi, listKeShi)
make_pai_ming(sheetSheQu, listSheQu, 1)


def inset_he_ji_bei_zhu(sheet_he_ji, str_bei_zhu, is_she_qu=0):
    rowCount = sheet_he_ji.max_row
    colCount = sheet_he_ji.max_column
    numLast = 0
    colDeFenHeJi = get_col_index(sheet_he_ji, 2, '得分')
    colPaiMingHeJi = get_col_index(sheet_he_ji, 2, '排名')
    for rowHeJi in range(4, rowCount+1):
        sheet_he_ji.cell(rowHeJi, 1).value = rowHeJi-3
        if rowHeJi != 4:
            valueDeFen = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
            if valueDeFen is not None:
                if valueDeFen == numLast:
                    sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi-1, colPaiMingHeJi).value
                else:
                    sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi, 1).value
                numLast = valueDeFen
        else:
            sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = 1
            numLast = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
    # get_column_letter(rowCount+1)
    strHeJi = 'A{}:B{}'.format(rowCount+1, rowCount+1)
    sheet_he_ji.merge_cells(range_string=strHeJi)
    sheet_he_ji.cell(rowCount+1, 1).value = '合计'
    if is_she_qu == 1:
        sheet_he_ji.merge_cells(range_string='A{}:AJ{}'.format(rowCount + 2, rowCount + 2))
        # strHeJi2 = 'A{}:AJ{}'.format(rowCount + 2, rowCount + 2)
    else:
        sheet_he_ji.merge_cells(range_string='A{}:AI{}'.format(rowCount + 2, rowCount + 2))
        # strHeJi2 = 'A{}:AI{}'.format(rowCount + 2, rowCount + 2)
    # sheet_he_ji.merge_cells(range_string=strHeJi2)
    sheet_he_ji.cell(rowCount + 2, 1).value = str_bei_zhu
    sheet_he_ji.row_dimensions[rowCount + 2].height = 50
    for colHeJi in range(3, colCount+1):
        valueColCell = sheet_he_ji.cell(3, colHeJi).value
        colAvai = 0
        if valueColCell is not None:
            if valueColCell.find('率') == -1:
                colAvai = colHeJi
        else:
            valueColCell2 = sheet_he_ji.cell(2, colHeJi).value
            if valueColCell2 is not None:
                if valueColCell2 == '考核总量' or valueColCell2 == '裸分得分' or valueColCell2 == '裸分失分':
                    colAvai = colHeJi
        if colAvai != 0:
            key = sheet_he_ji.cell(rowCount + 1, colAvai).coordinate
            # 求和的开始单元格地址
            start = sheet_he_ji.cell(4, colAvai).coordinate
            # 求和的结束单元格地址
            end = sheet_he_ji.cell(rowCount, colAvai).coordinate
            sheet_he_ji[key] = f'=SUM({start}:{end})'


strMainKeshi = '备注：\r\n1.未接单的科室未在上表内列出\r\n2.' + strMain
inset_he_ji_bei_zhu(sheetKeShi, strMainKeshi)
strMainSheQu = '备注：\r\n1.未接单的社区（村）未在上表内列出\r\n2.' + strMain
inset_he_ji_bei_zhu(sheetSheQu, strMainSheQu)
# def inset_into_sheet_in_line(line)
bookKaoHe.save(fileResultSave)
logger.debug('用时{}秒'.format(round(time.time() - timeStart, 2)))



